<report xmlns="http://developer.cognos.com/schemas/report/12.0/" expressionLocale="en-us" useStyleVersion="10"><!--RSU-SPC-0093 The report specification was upgraded from "http://developer.cognos.com/schemas/report/11.0/" to "http://developer.cognos.com/schemas/report/12.0/" at 2016-8-5. 9:22:15-->
				<modelPath>/content/folder[@name=&apos;Packages&apos;]/package[@name=&apos;Inventory&apos;]/model[@name=&apos;model&apos;]</modelPath>
				<drillBehavior/>
				<queries>
					<query name="Query1">
						<source>
							
						<sqlQuery dataSource="dwh_inventory" name="SQL1" type="passThrough">
			<sqlText>SELECT
A.DataCenter AS &apos;Datacenter&apos;,
A.Controller_Node AS &apos;Filer&apos;,
A.FlexVol_Name AS &apos;FlexVol_Name&apos;,
SUM(A.Total_Prov_GB) AS &apos;Total&apos;,
SUM(A.Total_Used_GB) AS &apos;Total Used&apos;,
SUM(A.Total_Free_GB) AS &apos;Total Free&apos;,
A.SS_All_GB AS &apos;SSAllocGB&apos;,
A.SS_Used_GB AS &apos;SSUsedGB&apos;,
SUM(A.Sis_Used_GB) AS &apos;Sis Used&apos;,
SUM(B.Sis_Free_GB) AS &apos;Sis Saved&apos;,
AVG(A.PctSaved) AS &apos;%Saved&apos;,
SUM(A.v2dd) AS &apos;v2dd&apos;,
SUM(A.vdd) AS &apos;vdd&apos;
 
FROM

(SELECT 
  storage.dataCenter AS &apos;DataCenter&apos;,
  SUBSTR(dwh_inventory.storage_pool.name, 1, LOCATE(&apos;:&apos;, dwh_inventory.storage_pool.name) - 1) AS &apos;Controller_Node&apos;,
  -- business_entity.lob AS &apos;Line_Of_Business&apos;,
  internal_volume.id AS &apos;FlexVol_ID&apos;,
  internal_volume.name AS &apos;FlexVol_Name&apos;,
  ROUND(SUM(internal_volume.totalAllocatedCapacityMB) / 1024, 2) AS &apos;Total_Prov_GB&apos;,
  ROUND(SUM(internal_volume.totalUsedCapacityFromDeviceMB) / 1024, 2) AS &apos;Total_Used_GB&apos;,
  ROUND(SUM(internal_volume.snapshotAllocatedCapacityMB) / 1024, 2) AS &apos;SS_All_GB&apos;,
  ROUND(SUM(internal_volume.snapshotUsedCapacityMB) / 1024, 2) AS &apos;SS_Used_GB&apos;,
  ROUND((SUM(internal_volume.totalAllocatedCapacityMB) - SUM(internal_volume.totalUsedCapacityFromDeviceMB)) /1024, 2) AS &apos;Total_Free_GB&apos;,
  ROUND(SUM(internal_volume.totalUsedCapacityFromDeviceMB) / 1024, 2) AS &apos;Sis_Used_GB&apos;,
  ROUND((SUM(dwh_inventory.internal_volume.totalUsedCapacityFromDeviceMB) * IFNULL(((1 - dwh_inventory.internal_volume.dedupeRatio)/dwh_inventory.internal_volume.dedupeRatio),0))/1024, 2) AS &apos;Sis_Free_GB&apos;,
  IFNULL(ROUND(AVG((1 - dwh_inventory.internal_volume.dedupeRatio)), 2), 0) AS &apos;PctSaved&apos;,
  SUM(IF(internal_volume.dedupeEnabled = 0 AND internal_volume.name NOT LIKE &apos;%vol0%&apos; OR &apos;%swap%&apos; AND internal_volume.id NOT IN(dr_internal_volume_replica.targetInternalVolumeId), &apos;1&apos;, &apos;0&apos;)) AS &apos;v2dd&apos;,
  SUM(IF(internal_volume.dedupeEnabled = 1 AND internal_volume.name NOT LIKE &apos;%vol0%&apos; OR &apos;%swap%&apos; AND internal_volume.id NOT IN(dr_internal_volume_replica.targetInternalVolumeId), &apos;1&apos;, &apos;0&apos;)) AS &apos;vdd&apos;,
  (SUM(IF(internal_volume.dedupeEnabled = 0 AND internal_volume.name NOT LIKE &apos;%vol0%&apos; OR &apos;%swap%&apos; AND internal_volume.id NOT IN(dr_internal_volume_replica.targetInternalVolumeId), &apos;1&apos;, &apos;0&apos;)) + SUM(IF(internal_volume.dedupeEnabled = 1 AND internal_volume.name NOT LIKE &apos;%vol0%&apos; OR &apos;%swap%&apos; AND internal_volume.id NOT IN(dr_internal_volume_replica.targetInternalVolumeId), &apos;1&apos;, &apos;0&apos;))) AS &apos;v2dd+vdd&apos;,
  COUNT(internal_volume.id) AS &apos;NoVols&apos;,
 IFNULL(ROUND((SUM(IF(internal_volume.dedupeEnabled = 1 AND internal_volume.name NOT LIKE &apos;%vol0%&apos; OR &apos;%swap%&apos; AND internal_volume.id NOT IN(dr_internal_volume_replica.targetInternalVolumeId), &apos;1&apos;, &apos;0&apos;)) / (SUM(IF(internal_volume.dedupeEnabled = 0 AND internal_volume.name NOT LIKE &apos;%vol0%&apos; OR &apos;%swap%&apos; AND internal_volume.id NOT IN(dr_internal_volume_replica.targetInternalVolumeId), &apos;1&apos;, &apos;0&apos;)) + SUM(IF(internal_volume.dedupeEnabled = 1 AND internal_volume.name NOT LIKE &apos;%vol0%&apos; OR &apos;%swap%&apos; AND internal_volume.id NOT IN(dr_internal_volume_replica.targetInternalVolumeId), &apos;1&apos;, &apos;0&apos;)))), 2), 0) AS &apos;vdd/(v2dd+vdd)&apos;
 
FROM

internal_volume

JOIN STORAGE ON internal_volume.storageId = storage.id
JOIN storage_pool ON internal_volume.storagePoolId = storage_pool.id
LEFT JOIN internal_volume_to_application ON internal_volume.id = internal_volume_to_application.InternalVolumeId
LEFT JOIN application ON internal_volume_to_application.applicationId = application.id
LEFT JOIN business_entity ON application.businessEntityId = business_entity.id
LEFT JOIN dr_internal_volume_replica ON internal_volume.id = dr_internal_volume_replica.targetInternalVolumeId

GROUP BY SUBSTR(dwh_inventory.storage_pool.name, 1, LOCATE(&apos;:&apos;, dwh_inventory.storage_pool.name) - 1), dwh_inventory.internal_volume.name) A JOIN

(SELECT 
  internal_volume.id AS &apos;FlexVol_ID&apos;,
  ROUND((SUM(dwh_inventory.internal_volume.totalUsedCapacityFromDeviceMB) * IFNULL(((1 - dwh_inventory.internal_volume.dedupeRatio)/dwh_inventory.internal_volume.dedupeRatio),0))/1024, 2) AS &apos;Sis_Free_GB&apos;

FROM
  internal_volume
  
  GROUP BY internal_volume.name) B ON A.FlexVol_ID = B.FlexVol_ID
  
GROUP BY A.Controller_Node, A.FlexVol_Name</sqlText>
		<mdProjectedItems><mdProjectedItem name="Datacenter"/><mdProjectedItem name="Filer"/><mdProjectedItem name="FlexVol_Name"/><mdProjectedItem name="Total"/><mdProjectedItem name="Total Used"/><mdProjectedItem name="Total Free"/><mdProjectedItem name="SSAllocGB"/><mdProjectedItem name="SSUsedGB"/><mdProjectedItem name="Sis Used"/><mdProjectedItem name="Sis Saved"/><mdProjectedItem name="%Saved"/><mdProjectedItem name="v2dd"/><mdProjectedItem name="vdd"/></mdProjectedItems></sqlQuery></source>
						<selection><dataItem name="Datacenter"><expression>[SQL1].[Datacenter]</expression></dataItem><dataItem name="Filer"><expression>[SQL1].[Filer]</expression></dataItem><dataItem name="FlexVol_Name"><expression>[SQL1].[FlexVol_Name]</expression></dataItem><dataItem name="Total"><expression>[SQL1].[Total]</expression></dataItem><dataItem name="Total Used"><expression>[SQL1].[Total Used]</expression></dataItem><dataItem name="Total Free"><expression>[SQL1].[Total Free]</expression></dataItem><dataItem name="SSAllocGB"><expression>[SQL1].[SSAllocGB]</expression></dataItem><dataItem name="SSUsedGB"><expression>[SQL1].[SSUsedGB]</expression></dataItem><dataItem name="Sis Used"><expression>[SQL1].[Sis Used]</expression></dataItem><dataItem name="Sis Saved"><expression>[SQL1].[Sis Saved]</expression></dataItem><dataItem name="%Saved"><expression>[SQL1].[%Saved]</expression></dataItem><dataItem name="v2dd"><expression>[SQL1].[v2dd]</expression></dataItem><dataItem name="vdd"><expression>[SQL1].[vdd]</expression></dataItem></selection>
					</query>
				</queries>
				<layouts>
					<layout>
						<reportPages>
							<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
								<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
									<contents>
										<list horizontalPagination="true" name="List1" refQuery="Query1" rowsPerPage="3000">
											
											
											
											<noDataHandler>
												<contents>
													<block>
														<contents>
															<textItem>
																<dataSource>
																	<staticValue>No Data Available</staticValue>
																</dataSource>
																<style>
																	<CSS value="padding:10px 18px;"/>
																</style>
															</textItem>
														</contents>
													</block>
												</contents>
											</noDataHandler>
											<style>
												<defaultStyles>
													<defaultStyle refStyle="ls"/>
												</defaultStyles>
												<CSS value="border-collapse:collapse"/>
											</style>
										<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Datacenter"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Datacenter"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Filer"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Filer"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="FlexVol_Name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="text-align:left"/></style><contents><textItem><dataSource><dataItemValue refDataItem="FlexVol_Name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Total"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Total"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Total Used"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Total Used"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Total Free"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Total Free"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="SSAllocGB"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="SSAllocGB"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="SSUsedGB"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="SSUsedGB"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Sis Used"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Sis Used"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Sis Saved"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Sis Saved"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="%Saved"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="%Saved"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="v2dd"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="v2dd"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="vdd"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="vdd"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
									</contents>
								</pageBody>
								<pageHeader>
									<contents>
										<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
											<contents>
												<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
													<dataSource>
														<staticValue>Deduplication by Flexvol Report</staticValue>
													</dataSource>
												</textItem>
											</contents>
										</block>
									</contents>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="ph"/>
										</defaultStyles>
										<CSS value="padding-bottom:10px"/>
									</style>
								</pageHeader>
								<pageFooter>
									<contents>
										<table>
											<tableRows>
												<tableRow>
													<tableCells>
														<tableCell>
															<contents>
																<date>
																	<style>
																		<dataFormat>
																			<dateFormat/>
																		</dataFormat>
																	</style>
																</date>
															</contents>
															<style>
																<CSS value="vertical-align:top;text-align:left;width:25%"/>
															</style>
														</tableCell>
														<tableCell>
															<contents>
																<pageNumber/>
															</contents>
															<style>
																<CSS value="vertical-align:top;text-align:center;width:50%"/>
															</style>
														</tableCell>
														<tableCell>
															<contents>
																<time>
																	<style>
																		<dataFormat>
																			<timeFormat/>
																		</dataFormat>
																	</style>
																</time>
															</contents>
															<style>
																<CSS value="vertical-align:top;text-align:right;width:25%"/>
															</style>
														</tableCell>
													</tableCells>
												</tableRow>
											</tableRows>
											<style>
												<defaultStyles>
													<defaultStyle refStyle="tb"/>
												</defaultStyles>
												<CSS value="border-collapse:collapse;width:100%"/>
											</style>
										</table>
									</contents>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="pf"/>
										</defaultStyles>
										<CSS value="padding-top:10px"/>
									</style>
								</pageFooter>
							</page>
						</reportPages>
					</layout>
				</layouts>
			<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" output="no" value="true"/><XMLAttribute name="listSeparator" output="no" value=","/><XMLAttribute name="RS_modelModificationTime" output="no" value="2016-05-03T17:14:53.899Z"/></XMLAttributes><reportName>Deduplication by Flexvol Report</reportName></report>